package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import ModelLayer.PartOrder;
import ModelLayer.Product;

public class DbPartOrder implements IfDbPartOrder
{
	private Connection con;
	
	public DbPartOrder()
	{
		con = DbConnection.getInstance().getDBcon();
	}
	
	public ArrayList<PartOrder> getAllPartOrder(boolean retriveAssociation) 
	{
        return miscWhere("", retriveAssociation);
	}
	
	private ArrayList<PartOrder> miscWhere(String wClause, boolean retrieveAssociation)
	{
        ResultSet results;
	    ArrayList<PartOrder> list = new ArrayList<PartOrder>();	
		
	    String query =  buildQuery(wClause);
  
            try
            { 
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	
	
		while( results.next() )
		{
	     	 PartOrder partOrderObject = new PartOrder();
	     	partOrderObject = buildPartOrder(results);	
                 list.add(partOrderObject );	
		}
                 stmt.close();     
		}
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
	
	private PartOrder singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		PartOrder partOrderObject = new PartOrder();
                
	        String query =  buildQuery(wClause);
                System.out.println(query);
		try
		{ 
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() )
	 		{
                            partOrderObject = buildPartOrder(results);
                            stmt.close();
			}
                        else
                        {
                            partOrderObject = null;
                        }
		}	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return partOrderObject;
	}
	
	private String buildQuery(String wClause)
	{
	    String query="SELECT partOrderID, amount, subTotal, product, orderID FROM partOrder";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}

	private PartOrder buildPartOrder(ResultSet results)
    {   
		PartOrder partOrderObject = new PartOrder();
        try
        {        	
      	  	partOrderObject.setAmount(results.getInt("amount"));
      	  	partOrderObject.setSubTotal(results.getDouble("subTotal"));
      	  	partOrderObject.setProduct(new Product(results.getInt("product")));
      	  	partOrderObject.setOrderID(results.getInt("OrderID"));
      	  	
        }
       catch(Exception e)
       {
           System.out.println("There is a failure in building the Supplier");
       }
       return partOrderObject;
    }
	
	public PartOrder findPartOrderByPartOrderID(int partOrderID, boolean retriveAssociation) 
	{
		String wClause = "partOrderID = '" + partOrderID + "''";
        System.out.println("Search PartOrder " + wClause);
        return singleWhere(wClause, retriveAssociation);
	}
	
	public int insertPartOrder(PartOrder PartOrder) throws Exception 
	{
		int lol = -1;
		String query="INSERT INTO partOrder(amount, subTotal, product, orderID)  VALUES('"+
				PartOrder.getAmount()  + "','"  +
				PartOrder.getSubTotal()  + "','"  +
				PartOrder.getProduct() + "','" +
				PartOrder.getOrderID() + "','";
		try
		{ 
	          Statement stmt = con.createStatement();
	          stmt.setQueryTimeout(5);
	     	  lol = stmt.executeUpdate(query);
	          stmt.close();
	    }
	       catch(SQLException ex)
	       {
	          System.out.println("PartOrder is not created");
	          throw new Exception ("PartOrder is not inserted correct");
	       }
	       return(lol);
	}
	
	public int updatePartOrder(PartOrder partOrder) 
	{
		PartOrder partOrderObject  = partOrder;
		int lol = -1;

		String query="UPDATE partOrder SET "+
		 	  "Amount ='"+ partOrderObject.getAmount()+ "', " +
		 	  "SubTotal ='"+ partOrderObject.getSubTotal() + "', " +
                          "Product ='"+ partOrderObject.getProduct() + "', " +
                          "OrderID ='"+ partOrderObject.getOrderID() + "' " +
                          "WHERE PartOrderID = '" + partOrderObject.getPartOrderID() + "'";
                System.out.println("Update query:" + query);
  		try
  		{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	lol  = stmt.executeUpdate(query);

	 	 	stmt.close();
		}
	 	catch(Exception ex)
	 	{
	 	 	System.out.println("Update exception in PartOrder db: " + ex);
	  	}
		return(lol);
	}
	
	public int deletePartOrder(int partOrderID)
	{
               int lol = -1;
	  
	  	String query="DELETE FROM partOrder WHERE partOrderID = '" +
				partOrderID + "'";
                System.out.println(query);
	  	try{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	lol  = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}
   	        catch(Exception ex)
   	        {
	 	  	System.out.println("Delete exception in partOrder db: "+ex);
   	        }
		return(lol);
	}
}